#include <head.h>
#include "share.h"
#include <sqlite3.h>

sqlite3 *open_db(const char *filename)
{
	int ret;//返回值判断
	sqlite3 *ppDb;//指向数据库的指针

	ret = sqlite3_open(filename, &ppDb);//打开数据库
	if(ret != SQLITE_OK){
		printf("%s\n", sqlite3_errmsg(ppDb));
		return NULL;
	}else{
		printf("open datebase success\n");
		return ppDb;
	}
}

int create_usr_table(sqlite3 *ppDb)
{
	int ret;
	char *errmsg;

	ret = sqlite3_exec(ppDb, "create table if not exists usr(id int primary key, name text, password int, sex text, age int, wage int)", NULL, NULL, &errmsg);
	if(ret != SQLITE_OK){
		printf("%s\n", errmsg);
		return -1;
	}else{
		printf("create or open table success\n");
	}
	return 0;
}

int insert_usrmsg(sqlite3 *ppDb, MSG usr)
{
	int ret;
	char *errmsg;
	char sql[N] = {};

	sprintf(sql, "insert into usr values('%d', '%s', '%d', '%c', '%d', '%d')", usr.id, usr.name, usr.password, usr.sex, usr.age, usr.wage);

	ret = sqlite3_exec(ppDb, sql, NULL, NULL, &errmsg);
	if(ret != SQLITE_OK){
		printf("insert %s error :%s\n", usr.name, errmsg);
		return -1;
	}else{
		printf("insert %s success\n", usr.name);
		return 0;
	}
}

int select_usrmsg(sqlite3 *ppDb, MSG usr)
{
	int ret;
	int pnRow;	
	int pnColumn;
	char *pzErrmsg;
	char sql[N] = {};
	char **pazResult;

	sprintf(sql, "select id from usr where id = '%d'", usr.id);

	ret = sqlite3_get_table(ppDb, sql, &pazResult, &pnRow, &pnColumn, &pzErrmsg);
	if(ret != SQLITE_OK){
		printf("select %s msg error :%s\n", usr.name, pzErrmsg);
		return -1;
	}else{
		if(pnRow == 0){
			printf("%s trying to login\n", usr.name);
			return 0;
		}
		if(pnRow == 1){
			printf("%s login\n", usr.name);
			return 1;
		}
	}
}

int usr_callback(void *arg, int f_num, char **f_value, char **f_name)
{
	int ret;
	ARG *parg = (ARG *)arg;

	sprintf(parg->msg.data, "%s : %s : %s : %s : %s : %s", f_value[0], f_value[1], f_value[2], f_value[3], f_value[4], f_value[5]);

	ret = send(parg->fd, &parg->msg, sizeof(MSG), 0);
	usleep(5);
	if(ret < 0){
		err_log("fail to send\n");
	}
	return 0;
}

int select_usr(ARG *arg)
{
	int ret;
	char *errmsg;
	char sql[N] = {};

	sprintf(sql, "select * from usr where id = '%d'", arg->msg.id);

	ret = sqlite3_exec(arg->db, sql, usr_callback, arg, &errmsg);
	if(ret != SQLITE_OK){
		printf("select %s record error :%s\n", arg->msg.name, errmsg);
		return -1;
	}else{
		printf("select %s record success\n", arg->msg.name);
		sprintf(arg->msg.data, "aaa");
		ret = send(arg->fd, &arg->msg, sizeof(MSG), 0);
		if(ret < 0){
		err_log("fail to send\n");
	}

		return 0;
	}
}

int select_usrall(ARG *arg)
{
	int ret;
	char *errmsg;
	char sql[N] = {};

	sprintf(sql, "select * from usr where id > 10");

	ret = sqlite3_exec(arg->db, sql, usr_callback, arg, &errmsg);
	if(ret != SQLITE_OK){
		printf("select record error :%s\n", errmsg);
		return -1;
	}else{
		printf("select record success\n");
		usleep(50);
		sprintf(arg->msg.data, "aaa");
		ret = send(arg->fd, &arg->msg, sizeof(MSG), 0);
		if(ret < 0){
		err_log("fail to send\n");
	}
		return 0;
	}
}

int delete_usr(sqlite3 *ppDb, MSG usr)
{
	char *errmsg;
	char sql[N] = {};

	sprintf(sql, "delete from usr where id = '%d'", usr.id);

	if(sqlite3_exec(ppDb, sql, NULL, NULL, &errmsg) != SQLITE_OK){
		printf("%s\n", errmsg);
		return -1;
	}else{
		printf("delete success");
	}
	return 0;
}

int update_usr(sqlite3 *ppDb, MSG usr)
{
	char *errmsg;
	char sql[N] = {};

	printf("%d : %d : %s", usr.user_type, __LINE__ ,__func__);

	if(usr.user_type == 1){
		printf("%d : %d : %s\n", usr.user_type, __LINE__ ,__func__);
		sprintf(sql, "update usr set age = '%d' ,wage = '%d' where id = '%d'", usr.age, usr.wage, usr.id);
	}
	else if(usr.user_type == 2){
		sprintf(sql, "update usr set age = '%d' ,password = '%d' where id = '%d'", usr.age, usr.password, usr.id);
		//sprintf(sql, "update usr set age = '%d' wage = '%d' where id = '%d'", usr.age, usr.wage, usr.id);
	}

	if(sqlite3_exec(ppDb, sql, NULL, NULL, &errmsg) != SQLITE_OK){
		printf("%s\n", errmsg);
		return -1;
	}else{
		printf("update success\n");
	}
	return 0;
}
